/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ergasia3h;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author user
 */
//Η κλάση DBManager για την επικοινωνία με την βάση δεδομένων 
public class DBManager {
    
    //Ιδιότητες
    private String database="ergasia_3";
    private String user="ergasia_3";
    private String password="ergasia_3";
    private String driver="org.apache.derby.jdbc.ClientDriver";
    private String connString="jdbc:derby://localhost:1527/";
    private Connection dBconnection;
    private Statement statement;
    
    // Ερωτήματα διαχείρισης δένδρου (αποθήκευση, μεταβολή, διαγραφή, αναζήτηση)
    private PreparedStatement isAdministrator;
    private PreparedStatement isRUser;           
    private PreparedStatement storeRestaurant;
    private PreparedStatement updateRestaurant;
    private PreparedStatement removeRestaurant;
    private PreparedStatement readRestaurant;
    private PreparedStatement readRestaurants;
    private PreparedStatement searchRestaurant;
    private PreparedStatement readPerioxi;
    private PreparedStatement readPerioxes;
    private PreparedStatement readPrice;
    private PreparedStatement readPrices;
    private PreparedStatement readKatigoria;
    private PreparedStatement readKatigories;
    private PreparedStatement readAxiologisi;
    private PreparedStatement storeAxiologisi;
    private PreparedStatement storeKratisi;
    private PreparedStatement readXristis;
    private PreparedStatement readXristes;
    
    
    
    //Δημιουργός της DBManager, προετοιμάζει τα απαιτούμενα ερωτήματα.
    
    public DBManager(){
        try {
            // Φόρτωση της κλάσης του οδηγού της βάσης δεδομένων
            Class.forName(driver);
            // Σύνδεση με τη βάση δεδομένων
            dBconnection=DriverManager.getConnection(connString+database,user,password);
            statement=(Statement) dBconnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            // Για τον διαχειριστή αναζήτηση στην βάση & ταυτοποίηση
            isAdministrator = dBconnection.prepareStatement("SELECT * FROM APP.DIAXEIRISTIS D, APP.XRISTIS X WHERE D.USERNAME=X.USERNAME AND X.USERNAME=? AND X.PASSWORD=?");
            
            // Για τον χρήστη αναζήτηση στην βάση & ταυτοποίηση
            isRUser = dBconnection.prepareStatement("SELECT * FROM APP.EGGEGRAMMENOSXRISTIS EX, APP.XRISTIS X WHERE E.USERNAME=X.USERNAME AND X.USERNAME=? AND X.PASSWORD=?");
            
            // Ενημέρωση στοιχείων Εστιατορίου
            updateRestaurant = dBconnection.prepareStatement("UPDATE APP.RESTAURANT SET ONOMA=?, ADDRESS=? PHONE=? PHOTO=? PLIROFORIES=? PERIGRAFI=? TIMIID=? PERIOXIID=? KATIGORIAID=?");
            
            // Διαγραφή Εστιατορίου
            removeRestaurant = dBconnection.prepareStatement("DELETE FROM APP.RESTAURANT WHERE ID=?");
            
            //Επιλογή όλων των Εστιατορίων 
            readRestaurants=dBconnection.prepareStatement("SELECT * FROM APP.RESTAURANT");
                        
            //Επιλογή Εστιατορίου με id         
            readRestaurant=dBconnection.prepareStatement("SELECT * FROM APP.RESTAURANT WHERE ID=?");
                                 
            //Επιλογή Εστιατορίου με βάση το όνομα, το εύρος τιμών, την περιοχή, την κατηγορία        
            searchRestaurant=dBconnection.prepareStatement("SELECT * FROM APP.RESTAURANT R, APP.PERIOXI P, APP.KATIGORIA K, APP.PRICES P WHERE R.PRICESID=P.ID AND R.PERIOXIID=P.ID AND R.KATIGORIAID=K.ID AND R.ONOMA=? AND P.EUROS=? AND P.ONOMA=? AND K.ONOMA=?");
            
             //Επιλογή όλων των Περιοχών        
             readPerioxes=dBconnection.prepareStatement("SELECT * FROM APP.PERIOXI");
                     
             //Επιλογή όλων των Κατηγοριών        
             readKatigories=dBconnection.prepareStatement("SELECT * FROM APP.KATIGORIA");       
             
             //Επιλογή όλων των Τιμών        
             readPrices=dBconnection.prepareStatement("SELECT * FROM APP.PRICES");    
             
            //Eπιλογή όλων των εγγεγραμμένων χρηστών
            readXristes = dBconnection.prepareStatement("SELECT * FROM APP.EGGEGRAMMENOSXRISTIS E, APP.XRISTIS X WHERE E.USERNAME = X.USERNAME");

             
             // Επιλογή μίας περιοχής
            readPerioxi = dBconnection.prepareStatement("SELECT * FROM APP.PERIOXI WHERE ID=?");
            // Επιλογή μίας τιμής
            readPrice = dBconnection.prepareStatement("SELECT * FROM APP.PRICES WHERE ID=?");
            // Επιλογή μίας κατηγορίας
            readKatigoria = dBconnection.prepareStatement("SELECT * FROM APP.KATIGORIA WHERE ID=?");
            // Επιλογή μίας αξιολόγησης με βάση το id ενός εστιατορίου
            readAxiologisi = dBconnection.prepareStatement("SELECT * FROM APP.AXIOLOGISI WHERE RESTAURANTID=?");
            // Επιλογή ενός εγγεγραμμένου χρήστη 
            readXristis = dBconnection.prepareStatement("SELECT * FROM APP.EGGEGRAMMENOSXRISTIS E, APP.XRISTIS X WHERE E.USERNAME = X.USERNAME AND E.USERNAME=?");

                        
    } catch (Exception exc){
      System.out.println(exc);  
    }
}

//Έλεγχος αν ο χρήστης είναι εγγεγραμένος χρήστης η διαχειριστής, επιστρέφει 0 αν δεν είναι τίποτα από τα δύο, 1 αν είναι εγγ.χρήστης, 2 αν είναι διαχειριστής
public int isTypeofuser(String username, String password){   
    
    int type=0;
    
        try {
        isRUser.setString(1, username);
        isRUser.setString(2, password);
        ResultSet rs1=isRUser.executeQuery();
        if(rs1.next()){
            type=1;
            return 1;
        }
        
        isAdministrator.setString(1, username);
        isAdministrator.setString(2, password);
        ResultSet rs2 = isAdministrator.executeQuery();
        if (rs2.next()){
            type = 2;
            return 2;
        }
        
        return type;
        
        } catch (SQLException ex) {
         Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
         return 0;
        }
}

//Αναζήτηση από λίστα εστιατορίων 
public Vector readRestaurants(){
        try {
            ResultSet rs = readRestaurants.executeQuery();
            Vector<Restaurant> restaurants = new Vector<Restaurant>();
            while (rs.next()){
                Restaurant r=new Restaurant();
                r.setId(rs.getInt("ID"));
                r.setOnoma(rs.getString("ONOMA"));
                r.setAddress(rs.getString("ADDRESS"));
                r.setPhone(rs.getString("PHONE"));
                r.setPlirofories(rs.getString("PLIROFORIES"));
                r.setPerigrafi(rs.getString("PERIGRAFI"));
                r.setPhoto(rs.getString("PHOTO"));
                
                int perioxiid = rs.getInt("perioxiid");
                int pricesid = rs.getInt("pricesid");
                int katigoriaid = rs.getInt("katigoriaid");
                
                readPerioxi.setInt(1, perioxiid);
                ResultSet rs2 = readPerioxi.executeQuery();
                Perioxi p = null;
                if (rs2.next()){
                    p = new Perioxi(rs2.getInt("id"), rs2.getString("name"));
                }
                r.setPerioxi(p);


                readPrice.setInt(1, pricesid);
                ResultSet rs3 = readPrice.executeQuery();
                Prices pr  = null;
                if (rs3.next()){
                    pr = new Prices(rs2.getInt("id"), rs3.getString("euros"));
                }
                r.setTimi(pr);
                
                
                readKatigoria.setInt(1, katigoriaid);
                ResultSet rs4 = readKatigoria.executeQuery();
                Katigoria k = null;
                if (rs4.next()){
                    k = new Katigoria(rs4.getInt("id"), rs4.getString("onoma"));
                }
                r.setKatigoria(k);
                
                restaurants.add(r);
                }
 
                return restaurants;
                } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
            
            }     
        }

//Επιλογή ενός εστιατορίου με βάση το id 
public Restaurant readRestaurant(int id){
        try {
            readRestaurant.setInt(1,id);
            ResultSet rs = readRestaurant.executeQuery();
            Restaurant r = new Restaurant();
            if (rs.next()){
               
                r.setId(rs.getInt("ID"));
                r.setOnoma(rs.getString("ONOMA"));
                r.setAddress(rs.getString("ADDRESS"));
                r.setPhone(rs.getString("PHONE"));
                r.setPlirofories(rs.getString("PLIROFORIES"));
                r.setPerigrafi(rs.getString("PERIGRAFI"));
                r.setPhoto(rs.getString("PHOTO"));
                
                int perioxiid = rs.getInt("perioxiid");
                int pricesid = rs.getInt("pricesid");
                int katigoriaid = rs.getInt("katigoriaid");
                
                readPerioxi.setInt(1, perioxiid);
                ResultSet rs2 = readPerioxi.executeQuery();
                Perioxi p = null;
                if (rs2.next()){
                    p = new Perioxi(rs2.getInt("id"), rs2.getString("onoma"));
                }
                r.setPerioxi(p);


                readPrice.setInt(1, pricesid);
                ResultSet rs3 = readPrice.executeQuery();
                Prices pr  = null;
                if (rs3.next()){
                    pr = new Prices(rs2.getInt("id"), rs3.getString("euros"));
                }
                r.setTimi(pr);
                
                
                readKatigoria.setInt(1, katigoriaid);
                ResultSet rs4 = readKatigoria.executeQuery();
                Katigoria k = null;
                if (rs4.next()){
                    k = new Katigoria(rs4.getInt("id"), rs4.getString("onoma"));
                }
                r.setKatigoria(k);
                
                }

                return r;
                } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
            
            }     
        }

//Επιλογή ενός εστιατορίου με βάση το όνομα, περιοχή, κατηγορία, εύρος τιμών 
public Vector <Restaurant> searchRestaurant(String onoma, String euros, String perioxi, String katigoria){
        try {
            searchRestaurant.setString(1,onoma);
            searchRestaurant.setString(2, perioxi);
            searchRestaurant.setString(3, euros);
            searchRestaurant.setString(4, katigoria);
            ResultSet rs = searchRestaurant.executeQuery();
            Vector<Restaurant>rest = new Vector<Restaurant>();
            while (rs.next()){
                Restaurant r=new Restaurant();
                r.setId(rs.getInt("ID"));
                r.setOnoma(rs.getString("ONOMA"));
                r.setAddress(rs.getString("ADDRESS"));
                r.setPhone(rs.getString("PHONE"));
                r.setPlirofories(rs.getString("PLIROFORIES"));
                r.setPerigrafi(rs.getString("PERIGRAFI"));
                r.setPhoto(rs.getString("Photo"));
                
                int perioxiid = rs.getInt("perioxiid");
                int pricesid = rs.getInt("pricesid");
                int katigoriaid = rs.getInt("katigoriaid");
                
                readPerioxi.setInt(1, perioxiid);
                ResultSet rs2 = readPerioxi.executeQuery();
                Perioxi p = null;
                if (rs2.next()){
                    p = new Perioxi(rs2.getInt("id"), rs2.getString("onoma"));
                }
                r.setPerioxi(p);


                readPrice.setInt(1, pricesid);
                ResultSet rs3 = readPrice.executeQuery();
                Prices pr  = null;
                if (rs3.next()){
                    pr = new Prices(rs2.getInt("id"), rs3.getString("euros"));
                }
                r.setTimi(pr);
                
                
                readKatigoria.setInt(1, katigoriaid);
                ResultSet rs4 = readKatigoria.executeQuery();
                Katigoria k = null;
                if (rs4.next()){
                    k = new Katigoria(rs4.getInt("id"), rs4.getString("onoma"));
                }
                r.setKatigoria(k);
                
                rest.add(r);
                
                }

                return rest;
                } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
            
            }     
        }

 

//Επιλογή ενός εγγεγραμμένου χρήστη  
public EggegrammenosXristis readXristis(String username){
        try {
            readXristis.setString(1,username);
            ResultSet rs = readXristis.executeQuery();
            EggegrammenosXristis e = null;
            while(rs.next()){
                e=newEggegrammenosXristis(rs.getString("USERNAME"), rs.getString("PASSWORD"));
               
                e.setOnoma(rs.getString("ONOMA"));
                e.setEponymo(rs.getString("EPONYMO"));
                e.setAddress(rs.getString("ADDRESS")); 
                e.setPhone(rs.getString("PHONE"));
                e.setEmail(rs.getString("EMAIL"));
                                  
                }

                return e;
                } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
            
            }     
        }

    private EggegrammenosXristis newEggegrammenosXristis(String string, String string0) {
        throw new UnsupportedOperationException("Not yet implemented");
    }
    
    //Ενημέρωση των στοιχείων ενός εστιατορίου
    public void updateRestaurant(Restaurant rest){
        try {
            updateRestaurant.setString(1, rest.getOnoma());
            updateRestaurant.setString(2, rest.getAddress());
            updateRestaurant.setString(3, rest.getPhone());
            updateRestaurant.setString(4, rest.getPlirofories());
            updateRestaurant.setString(5, rest.getPerigrafi());
            updateRestaurant.setString(6, rest.getPhoto());
            updateRestaurant.setInt(7, rest.getPerioxi().getId());
            updateRestaurant.setInt(8, rest.getTimi().getId());
            updateRestaurant.setInt(9, rest.getKatigoria().getId());
            updateRestaurant.setInt(10, rest.getId());
            updateRestaurant.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    //Διαγραφή ενός εστιατορίου από την βάση 
    public void removeRestaurant(Restaurant rest){
        try {
            removeRestaurant.setInt(1, rest.getId());
            removeRestaurant.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

//Αποθήκευση στην βάση μίας αξιολόγησης
public void storeAxiologisi(Axiologisi a){

try{

storeAxiologisi = dBconnection.prepareStatement("INSERT INTO APP.AXIOLOGISI (RESTAURANTID, EGGEGRAMMENOSXRISTISUSERNAME, PIOTITA, TIMES, EKSIPIRETISI) VALUES (?, ?, ?, ?, ?)");

storeAxiologisi.setInt(1,a.getRestaurant().getId());
storeAxiologisi.setString(2,a.getXristis().getUsername());
storeAxiologisi.setInt(3, a.getPiotita());
storeAxiologisi.setInt(4, a.getTimi());
storeAxiologisi.setInt(5, a.getEksipiretisi());

storeAxiologisi.executeUpdate();

   } catch (SQLException ex) {
      Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }



//Αποθήκευση στην βάση μίας κράτησης
    public void storeKratisi(Kratisi kr){
        try {
            storeKratisi = dBconnection.prepareStatement("INSERT INTO APP.KRATISI (RESTAURANTID, EGGEGRAMMENOSXRISTISUSERNAME) VALUES (?, ?)");

            storeKratisi.setInt(1, kr.getRestaurant().getId());
            storeKratisi.setString(2, kr.getXristis().getUsername());

            storeKratisi.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

//Aποθήκευση στην βάση ενός εστιατορίου
public int storeRestauranto(Restaurant rest){
        try {
            int[] autoCol=new int[1];
            autoCol[0]=1;

            storeRestaurant = dBconnection.prepareStatement("INSERT INTO APP.RESTAURANTO (ONOMA, ADDRESS, PHONE, PLIROFORIES, PERIGRAFI, PHOTO, PERIOXIID, PRICESID, KATIGORIAID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", autoCol);

            storeRestaurant.setString(1, rest.getOnoma());
            storeRestaurant.setString(2, rest.getAddress());
            storeRestaurant.setString(3, rest.getPhone());
            storeRestaurant.setString(4, rest.getPlirofories());
            storeRestaurant.setString(5, rest.getPerigrafi());
            storeRestaurant.setString(6, rest.getPhoto());
            storeRestaurant.setInt(7, rest.getPerioxi().getId());
            storeRestaurant.setInt(8, rest.getTimi().getId());
            storeRestaurant.setInt(9, rest.getKatigoria().getId());

            storeRestaurant.executeUpdate();
            ResultSet rs = storeRestaurant.getGeneratedKeys();
            if (rs != null) {
                rs.next();
                int storedID = rs.getInt(1);
                return storedID;
            }
            return -1;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }

    
}


